<?php

class Mdl_Findanexpert extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	function get_table() {
		$table = "dbo.Users";
		return $table;
	}

	//we also can do
	//var $table = "dfasfs";
	//and inside each function, we will change $table by $this->table

	function getListing_With_Where_Limit() {
		$sql = "SELECT DISTINCT PAG_TABLEXXX.total, PAG_TABLEXXX.Listing_ID, PAG_TABLEXXX.PropertyFor, PAG_TABLEXXX.StatusFID,
							PAG_TABLEXXX.CountryFID,PAG_TABLEXXX.CityFID,PAG_TABLEXXX.LocaltionFID,PAG_TABLEXXX.SubAreaFID,
							PAG_TABLEXXX.Tenure,PAG_TABLEXXX. StreetNameFID, PAG_TABLEXXX.ProjectNameFID,PAG_TABLEXXX.PropertyTypeFID,
							PAG_TABLEXXX.FloorArea_SM,PAG_TABLEXXX.FloorArea_SF,PAG_TABLEXXX.LandArea_SM,PAG_TABLEXXX.LandArea_SF,
							PAG_TABLEXXX.AskingPriceLocal,PAG_TABLEXXX.AskingPriceUSD,PAG_TABLEXXX.PriceInRMBPerSQMLocal, PAG_TABLEXXX.PriceInRMBPerSQMUSD,
							PAG_TABLEXXX.PriceUnit,PAG_TABLEXXX.ListingType,PAG_TABLEXXX.NewLaunch,PAG_TABLEXXX.Longitude, PAG_TABLEXXX.Latitude,
							PAG_TABLEXXX.Bedroom,PAG_TABLEXXX.Bathroom,PAG_TABLEXXX.MemberFID,PAG_TABLEXXX.ListingDate,
							PAG_TABLEXXX.LanguageFID_1,PAG_TABLEXXX.City_1,PAG_TABLEXXX.Location_1,PAG_TABLEXXX.Subarea_1,
							PAG_TABLEXXX.ProjectName_1,PAG_TABLEXXX.StreetName_1,
							PAG_TABLEXXX.PropertyType_1,CAST(PAG_TABLEXXX.[Description_1] AS NVARCHAR(MAX)) AS [Description_1]
					FROM
						(
							SELECT count(1) over( partition by 1 ) as total,  ROW_NUMBER() OVER ( ORDER BY  Listing_ID DESC) as rownum, l.*
							
							FROM dbo.view_listings_search l WHERE (1 = 1) AND StatusFID = 1 AND l.CountryFID = 8) AS PAG_TABLEXXX 
					WHERE rownum > 0 AND rownum <= 999999999";
		$query = $this->db->query($sql);

		return $query;
	}
	
	function getAgents($options = array()) {
		$tsql_callSP = '{call getAgentsInfoFromJLLSG(?,?,?,?,?,?,?,?,?,?,?,?)}';

		$params = array(
			array($options['language'], SQLSRV_PARAM_IN),
			array($options['country'], SQLSRV_PARAM_IN),
			array($options['propertytype'], SQLSRV_PARAM_IN),
			array($options['cities'], SQLSRV_PARAM_IN),
			array($options['locations'], SQLSRV_PARAM_IN),
			array($options['subarea'], SQLSRV_PARAM_IN),
			array($options['buyerorseller'], SQLSRV_PARAM_IN),
			array($options['agentname'], SQLSRV_PARAM_IN),
			array($options['offset'], SQLSRV_PARAM_IN),
			array($options['limit'], SQLSRV_PARAM_IN),
			array($options['orderby'], SQLSRV_PARAM_IN),
			array($options['listing_id'], SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();
		if ($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}
	
	function isAgentPhoto($assoc_id) {
		$bResult = true;
		
		$this->load->model('findanexpert/mdl_findanexpert');
		$result = $this->getagentphoto_($assoc_id);

		if($result) {
			if(!isset($result->Photo)) {
				$bResult = false;
			}
		} else {
			$bResult = false;
		}
		
		return $bResult;
	}
	
	function getAgents2($options = array()) {
		$db2 = $this->load->database('ams', TRUE);
		 
        $sql = "SELECT TOP 10 * FROM getAgents";
        $query = $db2->query($sql);
        
        if($query->num_rows() > 0) {
        	return $query->result();
        } else {
        	return false;
        }
	} 
	
	function getTotalAgents($data = array()) {
		$sql = "SELECT * FROM dbo.Users u";
		
		$sql .= " LEFT JOIN dbo.User_Profile up ON (u.User_ID = up.UserFID) WHERE up.LanguageFID = 1 AND u.StatusFID = 1 ORDER BY u.BusinessName";

		$query = $this->db->query($sql);

		return $query->num_rows();
	}
	
	public function getUser($listing_id) {
		$query = $this->db->query("SELECT AgentFID FROM Listings WHERE Listing_ID = " . (int)$listing_id . "");

		$row = $query->row();
		$agent_id = $row->AgentFID;
		return $agent_id;
	}
	
	public function getUser2($listing_id) {
		$query = $this->db->query("SELECT AgentFID FROM PrjDirectory WHERE ID = " . (int)$listing_id . "");

		$row = $query->row();
		$agent_id = $row->AgentFID;
		return $agent_id;
	}

	function getProjectWithWhere($options) {
		$tsql_callSP = '{call spProjectDirectoryFrontEnd(?,?,?,?,?,?,?,?,?,?,?)}';

		$params = array(
			array($options['country'], SQLSRV_PARAM_IN),
			array($options['propertytype'], SQLSRV_PARAM_IN),
			array($options['cities'], SQLSRV_PARAM_IN),
			array($options['locations'], SQLSRV_PARAM_IN),
			array($options['subarea'], SQLSRV_PARAM_IN),
			array($options['size_min'], SQLSRV_PARAM_IN),
			array($options['size_max'], SQLSRV_PARAM_IN),
			array($options['offset'], SQLSRV_PARAM_IN),
			array($options['limit'], SQLSRV_PARAM_IN),
			array($options['orderby'], SQLSRV_PARAM_IN),
			array($options['listing_id'], SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();
		if ($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}

	function get($order_by) {
		$table = $this->get_table();

		$this->db->order_by($order_by);
		$query = $this->db->get($table);

		return $query;
	}

	function get_with_limit($limit, $offset, $order_by) {
		$table = $this->get_table();
		$this->db->limit($limit, $offset);
		$this->db->order_by($order_by);
		$query = $this->db->query($table);

		return $query;
	}

	function get_where($id) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$query = $this->db->get($table);

		return $query;
	}

	function get_where_custom($col, $value) {
		$table = $this->get_table();
		$this->db->where($col, $value);
		$query = $this->db->get($table);

		return $query;
	}

	function _insert($data) {
		$table = $this->get_table();
		$this->db->insert($table, $data);
	}

	function _update($id, $data) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$this->db->update($table, $data);
	}

	function _delete($id) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$this->db->delete($table);
	}

	function count_where($column, $value) {
		$table = $this->get_table();
		$this->db->where($column, $value);
		$query = $this->db->get($table);
		$num_rows = $query->num_rows();
		return $num_rows;
	}

	function count_all() {
		$table = $this->get_table();
		$query = $this->db->get($table);
		$num_rows = $query->num_rows();

		return $num_rows;
	}

	function get_max() {
		$table = $this->get_table();
		$this->db->select_max('id');
		$query = $this->db->get($table);
		$row = $query->row();
		$id = $row->id;
		return $id;
	}

	function _custom_query($mysql_query) {
		$query = $this->db->query($mysql_query);
		return $query;
	}
	
	function getAgentListingMedia($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('ListingMedia');
		
		if($type == "Cover") {
			$where = array(
	        	'ListingFID' 	=> $id,
	        	'MediaType'	=> $this->db->escape_str($type)
			);
		} else {
			$where = array(
	        	'ListingFID' 	=> $id,
	        	'MediaType!='	=> 'floor plan'
	        	);
		}

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
			 
			return $arr;
		} else {
			return false;
		}
	}
	
	function getListing($User_ID) {
		$this->db->select('l.Listing_ID, l.Price_Min, l.PriceUnit, l.Link');
    	$this->db->from('dbo.viewProjectListing l');
		$this->db->join('dbo.View_Get_SGAgents_Filter u', 'u.User_ID = l.AgentFID');

		$where = array(
        	'u.User_ID'	=> $User_ID
		);

		$this->db->where($where);

		$query = $this->db->get();
		
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	function get_media($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');
		$where = array(
        	'PrjDirectoryFID' 	=>	$id,
        	'MediaType'			=>	$this->db->escape_str($type)
		);

		$this->db->where($where);

		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
			return $arr;
		} else {
			return false;
		}
	}

	function get_media_v2($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');

		$where = "PrjDirectoryFID=$id AND ((MediaType = 'Cover' and MediaOrientation = 'Landscape') OR (MediaType = 'Gallery' and MediaOrientation <> 'Portrait')) AND MediaType <> 'Floorplan'";

		$this->db->where($where);

		$this->db->order_by('MediaType ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
			return $arr;
		} else {
			return false;
		}
	}

	function getMediaForProjectDirectoryDetail($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');

		$where = "PrjDirectoryFID=$id AND MediaType IN ('Gallery', 'Cover')";

		$this->db->where($where);

		$this->db->order_by('MediaType ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				array_push($arr, $row);
			}
			return $arr;
		} else {
			return false;
		}
	}

	function getMediaFeaturedProjects($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' p.ID,pd.FileName,pd.FileExtension,pd.MediaType');
		$this->db->from('dbo.PrjDirectory p');
		$this->db->join('dbo.PrjDirMedia pd', 'p.ID = pd.PrjDirectoryFID'); // this joins the user table to topics
		$where = array(
			'pd.PrjDirectoryFID' => $id, 
			'p.ProjectType' => 'Featured', 
			'pd.MediaType' => $this->db->escape_str($type)
		);

		$this->db->where($where);

		$query = $this->db->get();

		$arr = array();
		$arr = $query->row();

		return $arr;
	}

	function get_proDir_media($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');
		$where = array(
        	'PrjDirectoryFID' 	=>	$id
		);

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
		}

		return $arr;
	}

	function getPremiumImage($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');
		$where = array(
        	'PrjDirectoryFID' 	=>	$id,
        	'MediaType'			=> $this->db->escape_str($type)
		);

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			$arr = $query->row();
			return $arr;
		} else {
			return false;
		}
	}

	function getPremiumImageV2($id, $type, $noofitem = 1, $theme = 'Lanscape') {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('PrjDirMedia');
		$where = array(
        	'PrjDirectoryFID' 	=>	$id,
        	'MediaType'			=> $this->db->escape_str($type),
        	'MediaOrientation'	=> $this->db->escape_str($theme)
		);

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			$arr = $query->row();
			return $arr;
		} else {
			return false;
		}
	}

	/*
	 * Author: Chi Nguyen
	 * get Type Of Unit By Project Directory Id
	 * access public
	 * param integer
	 * return array
	 */
	function getTypeOfUnit($pro_dir_id) {
		$this->db->select('ut.Name');
		$this->db->from('dbo.UnitType ut');
		$this->db->join('dbo.PrjDirUnit pu', 'pu.UnitTypeFID = ut.UnitType_ID');
		$this->db->join('dbo.PrjDirectory p', 'p.ID = pu.PrjDirecrotyFID');

		$where = array(
        	'pu.PrjDirecrotyFID' 	=>	$pro_dir_id
		);

		$this->db->where($where);
		$this->db->order_by('ut.Name ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				array_push($arr, $row);
			}
			return $arr;
		} else {
			return false;
		}
	}

	/*
	 * Author: Chi Nguyen
	 * get Tenure by Project Directory ID
	 * access public
	 * param integer
	 * return array
	 */
	function getTenure($pro_dir_id, $lang_id) {
		$this->db->select('t.Name');
		$this->db->from('dbo.Tenures t');
		$this->db->join('dbo.PrjDirectory p', 'p.TenureFID = t.Tenure_ID');

		$where = array(
        	'p.ID'				=> $pro_dir_id,
        	't.LanguageFID' 	=> $lang_id
		);

		$this->db->where($where);

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->row();
		} else {
			return false;
		}
	}

	/*
	 * Author: Chi Nguyen
	 * get Mode Type using project directory id
	 * access public
	 * param integer
	 * param integer
	 * return array
	 */
	function getModeType($pro_dir_id, $lang_id, $type) {
		$this->db->select('pl.FloorPlan_ID,pl.PrjDirectoryFID,pl.ModelType,pl.NoUnit,pl.Description,pm.FileName,pm.FileExtension,pm.MediaType');
		$this->db->from('dbo.PrjDirFloorPlan pl');
		$this->db->join('dbo.PrjDirMedia pm', 'pm.Media_ID = pl.MediaFID');

		$where = array(
        	'pl.PrjDirectoryFID'	=> $pro_dir_id,
        	'pl.LanguageFID' 		=> $lang_id,
        	'pm.MediaType'			=> $this->db->escape_str($type)
		);

		$this->db->where($where);

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				array_push($arr, $row);
			}
			return $arr;
		} else {
			return false;
		}
	}
	
	//Use this function for getting Thailand Agent Photo
	//The table which is called Agent that is being stored in main database
	function getAgentPhoto($userid) {
        $sql = "SELECT * FROM Users WHERE User_ID = " . $this->db->escape($userid) . "";

        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) {
            $result = $query->row();
        } else {
            $result = false;
        }
        
        return $result;
        
        /*
        
        if($result) {
        	//header('Content-Type: image/jpeg');
            //echo $result->Photo;
        	$path = HTTP_AGENT_IMAGE;
        	$data = $result->Photo;
        	$photoName = str_replace(' ', '', $result->BusinessName . '_' . $result->User_ID. '.jpg');
        	if(!file_exists($path . $photoName) && !empty($data)) {
        		file_put_contents($path . $photoName, $data);
        		
        		$image = new Resizeimage($path . $photoName);
				$image->resizeImage(170, 170, 'crop');
				$image->saveImage($path . $photoName);
				
        	}
        	
        	return $this->config->item('HTTP_AGENTS_MEDIA_IMAGE') . $photoName;
        } else {
        	return false;
        }
        */
    }
    
	
    function getagentphoto_($userid) {
    	 //load AMS db connection
		//$db2 = $this->load->database('ams', TRUE);
		 
        $sql = "SELECT Photo FROM dbo.Users a WHERE a.User_ID = " . $userid;
        //$query = $db2->query($sql);
        $query = $this->db->query($sql);
        
        if ($query->num_rows() > 0) {
           $result = $query->row();        
        } else {
           $result = false;
        }
        
        return $result;
    }
    
	function getAgentPhotoUsingUserId($userid) {
        $sql = "SELECT Photo FROM dbo.Users u WHERE u.User_ID = " . $userid;
        $query = $this->db->query($sql);
        
        if ($query->num_rows() > 0) {
           $result = $query->row();        
        } else {
           $result = false;
        }
        
        return $result;
    }
    
    
    //get total project listing using agent id
    function getTotalProjectListing($User_ID) {
    	$this->db->select();
		$this->db->from('dbo.viewProjectListing l');
		$this->db->join('dbo.View_Get_SGAgents_Filter u', 'u.User_ID = l.AgentFID');
		
		$where = array(
			'u.User_ID'	=> $User_ID
		);
		
		$this->db->where($where);
		
		$query = $this->db->get();
		
		return $query->num_rows();
    }
    
	//get recommendations using agent id
    function getRecommendations($User_ID) {
    	$this->db->select();
    	$this->db->from('dbo.Recommendation l');

		$where = array(
        	'l.AgentFID'	=> $User_ID
		);

		$this->db->where($where);

		$query = $this->db->get();
		
		return $query->num_rows();
    }
    
    //get Info of recommendations
    function getRecommendationsInfo($User_ID) {
		//get listing based on city id
		$sql = "select TOP 10 l.MemberFID,l.AgentFID, l.Content, l.CreatedDate,
					(SELECT TOP 1 ld.UserName FROM dbo.Member ld WHERE l.MemberFID = ld.Member_ID) AS UserName
				from dbo.Recommendation l
				where l.AgentFID = ". $User_ID . "
				order by l.CreatedDate";
		
		$query = $this->db->query($sql);

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
    }
    
    //get agent property type
	function getAgentPropertyType($User_ID) {
		$this->db->select('DISTINCT p.Name');
		$this->db->from('dbo.Users u');
		$this->db->join('dbo.Agent_PropertyType ap', 'u.User_ID = ap.UserFID');
		$this->db->join('dbo.PropertyType p', 'p.PropertyType_ID = ap.PropertyTypeFID');

		$where = array(
        	'u.User_ID'	=> $User_ID
		);

		$this->db->where($where);
		$this->db->order_by('p.Name');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get agent speciality
	function getAgentSpeciality($User_ID) {
		$this->db->select('DISTINCT p.SpecialtyName');
		$this->db->from('dbo.Users u');
		$this->db->join('dbo.Agent_Specialty ag', 'u.User_ID = ag.UserFID');
		$this->db->join('dbo.Specialty p', 'p.Specialty_ID = ag.SpecialtyFID');

		$where = array(
        	'u.User_ID'	=> $User_ID
		);

		$this->db->where($where);
		$this->db->order_by('p.SpecialtyName');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get speciality
	function getSpecialities($LanguageFID) {
		$this->db->select('Specialty_ID, SpecialtyName');
		$this->db->from('dbo.Specialty');

		$where = array(
        	'LanguageFID'	=> $LanguageFID
		);

		$this->db->where($where);
		$this->db->order_by('SpecialtyName');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get agent language
	function getAgentLanguage($User_ID) {
		$this->db->select('DISTINCT p.Name');
		$this->db->from('dbo.Users u');
		$this->db->join('dbo.Agent_Language ag', 'u.User_ID = ag.UserFID');
		$this->db->join('dbo.LanguageOfAgent p', 'p.Language_ID = ag.LanguageFID');

		$where = array(
        	'u.User_ID'	=> $User_ID
		);

		$this->db->where($where);
		$this->db->order_by('p.Name');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get price list
	function getPriceList($LanguageFID) {
		$this->db->select('Priceoption, Value');
		$this->db->from('dbo.Pricelist');

		$where = array(
        	'LanguageID'	=> $LanguageFID
		);

		$this->db->where($where);
		$this->db->order_by('Value');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get certifications
	function getCertifications($LanguageFID) {
		$this->db->select('Certification_ID, Name');
		$this->db->from('dbo.Certification');

		$this->db->order_by('Name');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	//get language
	function getLanguages($LanguageFID) {
		$this->db->select('Language_ID, Name');
		$this->db->from('dbo.LanguageOfAgent');

		$this->db->order_by('Name');

		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
}

?>
